# coding=utf-8
import pymysql
import pandas as pd
dataObj = {}
class Mysql_search():
    def __init__(self):
        self.dataObj = {}
        try:
            self.conn = pymysql.connect(host="47.100.19.231",
                                   port=3306,
                                   user="fcdb",
                                   password="fcdb",
                                   db="doquant",
                                   charset="utf8")

            self.cursor = self.conn.cursor()
        except pymysql.err as e:
            print("error" % e)

    def get_one(self,codeArr,startDate,endDate):
        sql =  "SELECT date, open, high,low, close, volume  FROM `t_futures_transaction_minute_data` WHERE symbol=%s AND DATE>%s AND DATE<%s"
        self.cursor.execute(sql,(codeArr[0],startDate,endDate))
        data = self.cursor.fetchall()
        # 处理查询结果，获取的数据转化为dataframe格式
        columnDes = self.cursor.description #获取连接对象的描述信息
        columnNames = [columnDes[i][0] for i in range(len(columnDes))] #获取列名
        df = pd.DataFrame([list(i) for i in data],columns=columnNames) #得到的data为二维元组，逐行取出，转化为列表，再转化为df
        df.set_index('date', inplace=True)
        self.dataObj[codeArr[0]] = df
        self.cursor.close()
        self.conn.close()
        return self.dataObj

    def get_two(self,codeArr,startDate,endDate):
        sql = "SELECT * FROM (SELECT date, open, high,low, close, volume  FROM `t_futures_transaction_minute_data` WHERE symbol=%s AND DATE>%s AND DATE<%s) A JOIN (SELECT date, open, high,low, close, volume FROM `t_futures_transaction_minute_data` WHERE symbol=%s AND DATE>%s AND DATE<%s) B ON A.DATE=B.DATE"
        self.cursor.execute(sql, (codeArr[0],startDate,endDate,codeArr[1],startDate,endDate))
        # 获取第一条查询结果，结果是元组
        data = self.cursor.fetchall()
         # 处理查询结果，获取的数据转化为dataframe格式
        columnDes = self.cursor.description #获取连接对象的描述信息
        columnNames = [columnDes[i][0] for i in range(len(columnDes))] #获取列名
        df = pd.DataFrame([list(i) for i in data],columns=columnNames) #得到的data

        df1 = df.iloc[:,[0,1,2,3,4,5]]
        df1.set_index('date', inplace=True)
        self.dataObj[codeArr[0]] = df1

        df2 = df.iloc[:,[6,7,8,9,10,11]]
        df2.set_index('date', inplace=True)
        self.dataObj[codeArr[1]] = df2

        self.cursor.close()
        self.conn.close()
        return self.dataObj

    def db_close(self):
        self.conn.close()

